﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SchemaExplorer;

using System.Data;
using System.Data.Common;

namespace CodeSmith.SchemaHelper.Util
{
    public static class OracleHelper
    {
       

        static Dictionary<string, string> SequenceColumns = new Dictionary<string, string>();

        public static bool GenInsertPreEnd(TableSchema table,out string pre,out string end)
        {
            bool result = HasSequenceColumn(table);
            pre = "";
            end = "";
            if (result)
            {
                pre = @"BEGIN
:p_" +  table.PrimaryKey.MemberColumns[0].Column.Name + ":= S_" + table.Name.ToUpper() + ".NEXTVAL;";
                end = ";END;";
            }
            return result;
        }

        public static bool HasSequenceColumn(TableSchema table)
        {
            bool result = false;
            if (table.PrimaryKey.MemberColumns.Count == 1)
            {
                result = IsSequenceColumn(table.PrimaryKey.MemberColumns[0]);
            }
            return result;
        }

        public static bool IsSequenceColumn(string columnName, TableSchema table)
        {
            ColumnSchema column = table.Columns[columnName];
            return IsSequenceColumn(column);           
        }
       

        public static bool IsSequenceColumn(ColumnSchema column)
        {
            bool result = false;
            //if (!System.Diagnostics.Debugger.IsAttached)
            //{
            //    System.Diagnostics.Debugger.Launch();
            //}
            
            if (column.Database.Provider.Name == "OracleSchemaProvider")
            {
                 bool correctDataType = column.DataType == DbType.Int16 || column.DataType == DbType.Int32 || column.DataType == DbType.Int64 || column.DataType == DbType.Decimal;
                 if (column.IsPrimaryKeyMember && correctDataType)
                {
                    string tableName = column.Table.Name;
                    string columnName = null;
                    if (SequenceColumns.ContainsKey(tableName))
                    {
                        columnName = SequenceColumns[tableName];
                        if (column.Name == columnName)
                        {
                            result = true;
                        }
                    }
                    else
                    {
                        using (DbConnection conn = DBHelper.GetConnection(column.Database.ConnectionString))
                        {
                            string commandText = string.Format("SELECT count(1) FROM All_Sequences WHERE Sequence_name='S_{0}' AND sequence_owner='{1}'", column.Table.Name.ToUpper(), column.Table.Owner.ToUpper());
                            DbCommand comm = conn.CreateCommand();
                            comm.CommandText = commandText;
                            conn.Open();
                            int cnt = Convert.ToInt32(comm.ExecuteScalar());
                            if (cnt > 0)
                            {
                                result = true;
                                SequenceColumns.Add(tableName, column.Name);
                            }
                            else
                            {
                                SequenceColumns.Add(tableName, "");
                            }
                        }
                    }
                   
                }
            }
            return result;
        }

        public static List<ColumnSchema> GetIndexColumns(TableSchema table, bool needNonColumns, out List<ColumnSchema> nonIndexColumns)
        {
            List<ColumnSchema> indexs = new List<ColumnSchema>();
            nonIndexColumns = new List<ColumnSchema>();
            List<string> indexKey = new List<string>();
            foreach (var index in table.Indexes)
            {
                if (index.IsPrimaryKey)
                {
                    foreach (var col in index.MemberColumns)
                    {
                        if (!indexKey.Contains(col.Name))
                        {
                            indexs.Add(col.Column);
                            indexKey.Add(col.Name);
                        }
                    }
                }
            }
            if (needNonColumns)
            {
                foreach (var col in table.Columns)
                {
                    if (!indexKey.Contains(col.Name))
                    {
                        nonIndexColumns.Add(col);
                    }
                }
            }

            return indexs;
        }

        

        public static void DoCombin(StringBuilder sb, ColumnSchema[] fields, int idx, int flag, string selectHeader, string otherCondition)
        {
            string pre = new string('\t', idx);
            sb.AppendFormat("{1}IF p_{0} IS NULL THEN \n", fields[idx].Name, pre);
            if (idx == fields.Length - 1)
            {
                writeSQL(sb, fields, idx, flag, selectHeader, otherCondition);
            }
            else
            {
                DoCombin(sb, fields, idx + 1, flag, selectHeader, otherCondition);
            }
            sb.AppendFormat("{0}ELSE\n", pre);
            flag |= (1 << idx);
            if (idx == fields.Length - 1)
            {
                writeSQL(sb, fields, idx, flag, selectHeader, otherCondition);
            }
            else
            {
                DoCombin(sb, fields, idx + 1, flag, selectHeader, otherCondition);
            }

            sb.AppendFormat("{0}END IF;\n", pre);
        }

        static void writeSQL(StringBuilder sb, ColumnSchema[] fields, int idx, int flag, string selectHeader, string otherCondition)
        {
            int temp = 1;
            bool first = true;
            string pre = new string('\t', idx);

            sb.Append(pre + "\t" + selectHeader);
            sb.AppendFormat("{0}\tWHERE\n", pre);

            for (int i = 0; i <= idx; i++)
            {
                temp = 1 << i;

                if ((flag & temp) == temp)
                {
                    if (first)
                    {
                        sb.AppendFormat("{1}\t\"{0}\" = p_{0}", fields[i].Name, pre);
                        first = false;
                    }
                    else
                    {
                        sb.AppendFormat(" AND \"{0}\" = p_{0}", fields[i].Name);
                    }
                }
            }
            if (first)
            {
                sb.AppendFormat("{0};\n", otherCondition);
            }
            else
            {
                sb.AppendFormat(" AND {0};\n", otherCondition);
            }
        }
    }
}
